Date and Time Functions: NOW(), CURDATE(), and DATE_FORMAT() in MySQL – Detailed Notes

Course: MySQL Training

Topic: Date and Time Functions: NOW(), CURDATE(), and DATE_FORMAT()

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Date and Time Functions in MySQL

Date and time functions are built-in MySQL functions that help retrieve the current date and time, format date values, extract date components, and perform date-related calculations. They are widely used in employee management systems, e-commerce websites, banking applications, booking platforms, billing systems, and reporting dashboards.

Databases frequently store information such as employee joining dates, customer registration dates, order timestamps, payment dates, appointment schedules, and product delivery dates. Date and time functions make it easier to process these values and present them in a readable format.

Three important MySQL date and time functions are:

• NOW(): Returns the current date and time.
• CURDATE(): Returns the current date without the time portion.
• DATE_FORMAT(): Formats a date or datetime value into a string according to a specified format.

For example, an application can use NOW() to record when an order is created, CURDATE() to identify the current business date, and DATE_FORMAT() to display an order date in a report-friendly format.

SELECT NOW() AS current_datetime;
SELECT CURDATE() AS current_date;
SELECT DATE_FORMAT('2026-10-16', '%d-%m-%Y') AS formatted_date;

The output of functions that return the current date or time depends on the MySQL server's session time zone. The displayed results also depend on when the query is executed.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand date and time values in MySQL.
• Explain the purpose of NOW(), CURDATE(), and DATE_FORMAT().
• Understand the syntax and output of each function.
• Retrieve the current date and time in SQL queries.
• Format dates using MySQL format specifiers.
• Display dates in different formats for reports and applications.
• Use date functions with table columns.
• Combine date functions with filtering, sorting, and grouping.
• Understand NULL handling, session time zones, and common errors.
• Apply date functions to practical database tasks.

3. Understanding Date and Time Data Types

Before using date functions, it is important to understand the main date and time data types available in MySQL.

The choice of data type depends on the purpose of the column. For example, an employee's joining date may use DATE, while an order creation timestamp may use DATETIME or TIMESTAMP, depending on the application's time-zone requirements.

4. NOW() Function in MySQL

The NOW() function returns the current date and time according to the MySQL session time zone. Its standard output includes the year, month, day, hours, minutes, and seconds.

4.1 Syntax of NOW()

NOW()
NOW(fsp)

Here, fsp is an optional fractional seconds precision value from 0 to 6. It controls how many digits are displayed after the decimal point in the seconds component.

4.2 Basic Example

SELECT NOW() AS current_datetime;

Example output:

+---------------------+
| current_datetime    |
+---------------------+
| 2026-10-16 14:30:45 |
+---------------------+

The date and time shown above are illustrative. Your actual result will depend on the date and time when the query runs and the configured session time zone.

4.3 Understanding the Output

A typical NOW() result follows this structure:

YYYY-MM-DD HH:MM:SS

• YYYY: Four-digit year.
• MM: Two-digit month.
• DD: Two-digit day of the month.
• HH: Hour in 24-hour format.
• MM: Minutes.
• SS: Seconds.

For example, 2026-10-16 14:30:45 represents 16 October 2026 at 14:30:45 in the relevant session time zone.

4.4 Using NOW(3) for Milliseconds

MySQL supports fractional seconds precision from 0 to 6. A precision of 3 displays three fractional digits, commonly used to represent millisecond precision.

SELECT NOW(3) AS current_datetime_ms;

Example output:

2026-10-16 14:30:45.123

The fractional value is illustrative. Use NOW(6) when microsecond precision is required and supported by the target column or expression.

4.5 Using NOW() with Table Data

Suppose an orders table stores information about online orders. The order_created_at column records when each order was created.

SELECT
    order_id,
    customer_id,
    order_created_at,
    NOW() AS query_datetime
FROM orders;

This query displays each order's recorded timestamp alongside the current date and time when the query runs.

4.6 Recording the Current Date and Time During INSERT

NOW() can be used to supply a date and time value when inserting a new record.

INSERT INTO orders
    (order_id, customer_id, order_created_at)
VALUES
    (1001, 25, NOW());

This example assumes the table has compatible columns and the supplied identifiers are valid. The new record receives the current date and time returned by NOW().

4.7 Updating a Timestamp

When a record is modified, NOW() can be used to record the modification time.

UPDATE orders
SET last_updated_at = NOW()
WHERE order_id = 1001;

Always use an appropriate WHERE clause when updating records. Without a filter, every row in the table may be updated.

4.8 Important Behavior of NOW()

MySQL evaluates NOW() using the time at which the statement began. Multiple references to NOW() within the same statement return the same statement-start time.

SELECT
    NOW() AS first_value,
    NOW() AS second_value;

Both values will normally be identical because they are evaluated using the same statement-start time.

4.9 NOW() and SYSDATE()

NOW() and SYSDATE() both return date and time values, but their timing behavior differs. NOW() represents the statement-start time, whereas SYSDATE() returns the time at which the function executes. This difference can matter in long-running statements and stored programs.

5. CURDATE() Function in MySQL

The CURDATE() function returns the current date without the time portion. It is useful when a query needs the current calendar date but does not need hours, minutes, or seconds.

5.1 Syntax of CURDATE()

CURDATE()

The function does not require any arguments.

5.2 Basic Example

SELECT CURDATE() AS current_date;

Example output:

+--------------+
| current_date |
+--------------+
| 2026-10-16   |
+--------------+

The date shown is illustrative. The returned value depends on when the query runs and the MySQL session's time-zone context.

5.3 Difference Between CURDATE() and NOW()

5.4 Finding Records Created Today

Suppose an orders table contains an order_date column with a DATE data type.

SELECT *
FROM orders
WHERE order_date = CURDATE();

This query retrieves orders whose date matches the current date.

5.5 Finding Records from the Current Month

You can compare the year and month of a date column with the current date.

SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
  AND MONTH(order_date) = MONTH(CURDATE());

This example is easy to understand, but applying functions to the indexed column may reduce the ability to use an ordinary index efficiently. A range-based condition is often preferable for large tables.

SELECT *
FROM orders
WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND order_date < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');

This second query defines the start of the current month and the start of the next month. It uses an inclusive lower bound and an exclusive upper bound, which is a useful pattern for date and datetime filtering.

5.6 Finding Past and Future Dates

CURDATE() can be combined with date arithmetic to identify dates before or after today.

-- Records before today
SELECT *
FROM appointments
WHERE appointment_date < CURDATE();

-- Records after today
SELECT *
FROM appointments
WHERE appointment_date > CURDATE();

-- Records scheduled for today
SELECT *
FROM appointments
WHERE appointment_date = CURDATE();

These examples assume that appointment_date is a DATE column. If the column stores a datetime, comparing it directly to CURDATE() can match only values at midnight for equality. For a full calendar day, use a range.

SELECT *
FROM appointments
WHERE appointment_datetime >= CURDATE()
  AND appointment_datetime < CURDATE() + INTERVAL 1 DAY;

5.7 CURDATE() and Current Date Aliases

MySQL also supports CURRENT_DATE and CURRENT_DATE() as synonyms for CURDATE().

SELECT
    CURDATE() AS using_curdate,
    CURRENT_DATE() AS using_current_date;

Both expressions return the current date.

6. DATE_FORMAT() Function in MySQL

The DATE_FORMAT() function formats a date or datetime value according to a specified format string. It is especially useful for reports, exports, user interfaces, and date labels.

For example, a database may store a date as 2026-10-16, while a report needs to display it as 16-10-2026 or 16 October 2026.

6.1 Syntax of DATE_FORMAT()

DATE_FORMAT(date, format)

Here:

• date: The date or datetime value to format.
• format: A string containing the desired layout and format specifiers.

The result is a string. It changes the displayed representation of a date; it does not change the stored date value or convert the underlying column into a different date.

6.2 Basic Example

SELECT DATE_FORMAT('2026-10-16', '%d-%m-%Y') AS formatted_date;
-- Result: 16-10-2026

In this example:

• %d represents the two-digit day.
• %m represents the two-digit month.
• %Y represents the four-digit year.

6.3 Formatting a Date in Different Ways

SELECT DATE_FORMAT('2026-10-16', '%Y-%m-%d') AS format_1;
-- Result: 2026-10-16

SELECT DATE_FORMAT('2026-10-16', '%d/%m/%Y') AS format_2;
-- Result: 16/10/2026

SELECT DATE_FORMAT('2026-10-16', '%m/%d/%Y') AS format_3;
-- Result: 10/16/2026

SELECT DATE_FORMAT('2026-10-16', '%d-%m-%y') AS format_4;
-- Result: 16-10-26

Choose the format required by the application or report. Be careful when displaying dates for international audiences because day-month-year and month-day-year formats can be confused.

6.4 Common DATE_FORMAT() Specifiers

Important: MySQL uses %m for the month and %i for minutes. These two specifiers are easy to confuse.

6.5 Displaying the Full Month Name

SELECT DATE_FORMAT('2026-10-16', '%d %M %Y') AS formatted_date;
-- Result: 16 October 2026

This format is useful for invoices, certificates, formal reports, and user-friendly interfaces.

6.6 Displaying the Abbreviated Month Name

SELECT DATE_FORMAT('2026-10-16', '%d %b %Y') AS formatted_date;
-- Result: 16 Oct 2026

Abbreviated month names can help reduce the space needed for dates in tables and dashboards.

6.7 Formatting Date and Time Together

SELECT DATE_FORMAT(
    '2026-10-16 14:30:45',
    '%d-%m-%Y %h:%i:%s %p'
) AS formatted_datetime;
-- Result: 16-10-2026 02:30:45 PM

The format combines the date with a 12-hour clock and an AM/PM indicator.

6.8 Formatting Time in 24-Hour Format

SELECT DATE_FORMAT(
    '2026-10-16 14:30:45',
    '%Y-%m-%d %H:%i:%s'
) AS formatted_datetime;
-- Result: 2026-10-16 14:30:45

Use %H for a 24-hour clock. Use %h or %I for a 12-hour clock.

6.9 Displaying the Weekday and Month

SELECT DATE_FORMAT(
    '2026-10-16',
    '%W, %d %M %Y'
) AS readable_date;

This format returns a readable date with the weekday name, day, full month name, and year. The weekday name depends on the supplied date and the locale settings of MySQL.

6.10 Formatting Current Date and Time

You can combine NOW() and DATE_FORMAT() to display the current date and time in a custom format.

SELECT DATE_FORMAT(
    NOW(),
    '%d-%m-%Y %h:%i:%s %p'
) AS current_datetime;

This query formats the current date and time as day-month-year followed by a 12-hour time and AM/PM indicator.

6.11 Formatting the Current Date

SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y') AS today_formatted;

This query uses CURDATE() to retrieve the current date and then formats it for display.

6.12 Handling NULL Values

If the date argument or format argument supplied to DATE_FORMAT() is NULL, the result is NULL.

SELECT DATE_FORMAT(NULL, '%d-%m-%Y') AS result;
-- Result: NULL

When the source date can be NULL, decide whether the report should show NULL, a fallback message, or another representation.

7. Difference Between NOW(), CURDATE(), and DATE_FORMAT()

8. Using Date Functions with Table Columns

Date functions become particularly useful when applied to real database records. Consider an employees table with the following structure.

8.1 Display Joining Dates in a Custom Format

SELECT
    employee_name,
    joining_date,
    DATE_FORMAT(joining_date, '%d-%m-%Y') AS formatted_joining_date
FROM employees;

8.2 Display the Current Date Beside Each Employee

SELECT
    employee_name,
    joining_date,
    CURDATE() AS current_date
FROM employees;

8.3 Display Current Date and Time Beside Each Employee

SELECT
    employee_name,
    created_at,
    NOW() AS query_datetime
FROM employees;

8.4 Display Joining Month and Year

SELECT
    employee_name,
    DATE_FORMAT(joining_date, '%M %Y') AS joining_month_year
FROM employees;

This format can be useful when preparing monthly employee reports.

9. Using Date Functions with WHERE Conditions

Date functions can be used to filter records according to a date or datetime condition.

9.1 Retrieve Orders Created Today

If order_date has a DATE data type, use:

SELECT *
FROM orders
WHERE order_date = CURDATE();

If order_created_at stores a datetime, use a range to retrieve every record created during the current calendar day.

SELECT *
FROM orders
WHERE order_created_at >= CURDATE()
  AND order_created_at < CURDATE() + INTERVAL 1 DAY;

9.2 Retrieve Orders from the Current Month

SELECT *
FROM orders
WHERE order_created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND order_created_at < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');

The query includes records from the first day of the current month up to, but not including, the first day of the following month. This pattern handles datetime values without needing to guess the last second of the month.

9.3 Retrieve Records Created in the Last Seven Days

SELECT *
FROM orders
WHERE order_created_at >= NOW() - INTERVAL 7 DAY
  AND order_created_at <= NOW();

This example retrieves records within a rolling seven-day period ending at the query's current date and time. If the intended period is seven calendar dates rather than a rolling 168-hour window, the condition should be adjusted accordingly.

10. Grouping and Reporting with Date Functions

Date functions are often used to group information into years, months, or days for business reporting.

10.1 Count Orders by Month

SELECT
    DATE_FORMAT(order_created_at, '%Y-%m') AS order_month,
    COUNT(*) AS total_orders
FROM orders
GROUP BY DATE_FORMAT(order_created_at, '%Y-%m')
ORDER BY order_month;

The query groups orders into year-month combinations and counts the number of records in each group.

10.2 Count Orders by Calendar Date

SELECT
    DATE(order_created_at) AS order_date,
    COUNT(*) AS total_orders
FROM orders
GROUP BY DATE(order_created_at)
ORDER BY order_date;

The DATE() function extracts the date portion from a datetime expression. This query groups all orders on the same calendar date together.

10.3 Display Monthly Sales

SELECT
    DATE_FORMAT(order_created_at, '%M %Y') AS sales_month,
    SUM(order_amount) AS total_sales
FROM orders
GROUP BY
    DATE_FORMAT(order_created_at, '%Y-%m'),
    DATE_FORMAT(order_created_at, '%M %Y')
ORDER BY MIN(order_created_at);

This query calculates total sales by month. It groups by the year-month key and the readable month label so that different years do not get combined under the same month name. The chronological sort uses the earliest date in each group.

11. NOW() vs CURDATE() in Practical Scenarios

12. Common Mistakes and Troubleshooting

13. Time Zone Considerations

MySQL's current-date and current-time functions use the session time zone. As a result, two database sessions configured for different time zones may observe different local date or time values at the same moment.

Check the current session and global time-zone settings with:

SELECT
    @@session.time_zone AS session_time_zone,
    @@global.time_zone AS global_time_zone;

The global setting and session setting may differ. The value SYSTEM indicates that the corresponding setting uses the system time zone.

For applications operating across multiple regions, define a clear storage and display strategy. For example, an application may store event timestamps consistently and convert them to the user's local time for display. Do not assume that every date and time data type automatically applies the same time-zone conversion rules.

14. Performance and Best Practices

• Use the right function: Use NOW() for date and time, CURDATE() for date only, and DATE_FORMAT() for display formatting.
• Store dates in date types: Prefer DATE, DATETIME, or TIMESTAMP as appropriate instead of storing dates as arbitrary text.
• Format at the presentation stage when appropriate: Keep date values in their proper types for filtering, sorting, and calculations.
• Use date ranges for filtering: For large tables, direct range predicates on indexed date or datetime columns are often more index-friendly than wrapping the column in a function.
• Use explicit aliases: Give formatted expressions readable names such as formatted_order_date.
• Be consistent with date formats: Choose a format appropriate for the users and avoid ambiguous day-month ordering.
• Check fractional seconds requirements: Use the precision needed by the application rather than adding unnecessary digits.
• Understand statement timing: Multiple calls to NOW() within one statement return the same statement-start time.
• Handle NULL values: Decide how missing dates should appear in reports and exports.
• Test boundary cases: Check month changes, year changes, midnight, and time-zone boundaries when writing date filters.

15. Mini Project: Order Date and Time Reporting

Project objective: Build a simple SQL report that displays order timestamps in readable formats and identifies orders created during the current day and month.

15.1 Sample Table Structure

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10,2),
    order_created_at DATETIME
);

15.2 Insert Sample Records

INSERT INTO orders
(order_id, customer_id, order_amount, order_created_at)
VALUES
(1001, 25, 2500.00, '2026-10-16 09:15:00'),
(1002, 26, 1800.00, '2026-10-15 13:45:00'),
(1003, 27, 3200.00, '2026-10-01 16:30:00'),
(1004, 25, 900.00, '2026-09-28 11:20:00');

15.3 Display Orders with Formatted Timestamps

SELECT
    order_id,
    customer_id,
    order_amount,
    order_created_at,
    DATE_FORMAT(order_created_at, '%d-%m-%Y') AS order_date,
    DATE_FORMAT(order_created_at, '%h:%i %p') AS order_time
FROM orders
ORDER BY order_created_at DESC;

15.4 Find Orders Created Today

SELECT *
FROM orders
WHERE order_created_at >= CURDATE()
  AND order_created_at < CURDATE() + INTERVAL 1 DAY;

15.5 Calculate Total Orders for the Current Month

SELECT
    COUNT(*) AS total_orders,
    SUM(order_amount) AS total_order_value
FROM orders
WHERE order_created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND order_created_at < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');

The results depend on the current date when the queries are executed. The inserted records use example dates, so the monthly and daily results will vary depending on the execution date.

15.6 Project Learning Outcomes

• Retrieve current date and time values.
• Display dates in user-friendly formats.
• Filter datetime records by calendar day.
• Build monthly reports using date ranges.
• Understand the difference between stored date values and formatted output.

16. Practice Exercises

1. Write a query to display the current date and time using NOW().
2. Write a query to display only the current date using CURDATE().
3. Format the date 2026-10-16 as 16-10-2026.
4. Format the date 2026-10-16 as 16 October 2026.
5. Display the current date in the format YYYY/MM/DD.
6. Display the current date and time using a 12-hour clock and AM/PM.
7. Display each employee's joining date as day-month-year.
8. Retrieve all orders created today when the timestamp is stored in a DATETIME column.
9. Count orders grouped by year and month.
10. Display the current month name and year in a single result column.
11. Display the current session time zone and global time zone.
12. Explain why filtering a DATETIME column with equality to CURDATE() may fail to return all records for today.

17. Practice Exercise Solutions

Solution 1: Current Date and Time

SELECT NOW() AS current_datetime;

Solution 2: Current Date

SELECT CURDATE() AS current_date;

Solution 3: Day-Month-Year Format

SELECT DATE_FORMAT('2026-10-16', '%d-%m-%Y') AS formatted_date;

Solution 4: Full Month Name

SELECT DATE_FORMAT('2026-10-16', '%d %M %Y') AS formatted_date;

Solution 5: Year/Month/Day Format

SELECT DATE_FORMAT(CURDATE(), '%Y/%m/%d') AS formatted_date;

Solution 6: Current Date and Time with AM/PM

SELECT DATE_FORMAT(NOW(), '%d-%m-%Y %h:%i:%s %p') AS formatted_datetime;

Solution 7: Format Employee Joining Dates

SELECT
    employee_name,
    DATE_FORMAT(joining_date, '%d-%m-%Y') AS formatted_joining_date
FROM employees;

Solution 8: Orders Created Today

SELECT *
FROM orders
WHERE order_created_at >= CURDATE()
  AND order_created_at < CURDATE() + INTERVAL 1 DAY;

Solution 9: Count Orders by Month

SELECT
    DATE_FORMAT(order_created_at, '%Y-%m') AS order_month,
    COUNT(*) AS total_orders
FROM orders
GROUP BY DATE_FORMAT(order_created_at, '%Y-%m')
ORDER BY order_month;

Solution 10: Current Month Name and Year

SELECT DATE_FORMAT(CURDATE(), '%M %Y') AS current_month_year;

Solution 11: Display Time-Zone Settings

SELECT
    @@session.time_zone AS session_time_zone,
    @@global.time_zone AS global_time_zone;

Solution 12: DATETIME Equality and CURDATE()

A DATETIME value includes both date and time. Comparing it directly to CURDATE() effectively compares against the date at midnight, so records with later times on the same day may not match. Use a range from the current date inclusive to the next date exclusive.

18. Interview Questions and Answers

Q1. What is the NOW() function in MySQL?

NOW() returns the current date and time according to the MySQL session time zone.

Q2. What is the CURDATE() function?

CURDATE() returns the current date without the time portion.

Q3. What is the difference between NOW() and CURDATE()?

NOW() returns both date and time, while CURDATE() returns only the date.

Q4. What is DATE_FORMAT() used for?

DATE_FORMAT() converts a date or datetime value into a string using the requested format specifiers.

Q5. What does %Y mean in DATE_FORMAT()?

%Y represents a four-digit year.

Q6. What is the difference between %m and %i?

%m represents the month, while %i represents minutes.

Q7. How do you display a date as DD-MM-YYYY?

SELECT DATE_FORMAT(CURDATE(), '%d-%m-%Y');

Q8. How do you display a date with a full month name?

SELECT DATE_FORMAT(CURDATE(), '%d %M %Y');

Q9. Does DATE_FORMAT() permanently change a date column?

No. It changes the returned representation into a string. The stored column value remains unchanged.

Q10. What happens when DATE_FORMAT() receives a NULL date?

The function returns NULL if the date argument or the format argument is NULL.

Q11. Why is CURDATE() useful in date filtering?

It provides the current calendar date and can be used to compare date columns with today or define date-based ranges.

Q12. Why should a DATETIME column be filtered using a range for a full day?

A DATETIME column contains a time component, so equality to a date value can match only values at midnight. A start-inclusive, end-exclusive range includes all times within the day.

Q13. What is the optional argument in NOW(3)?

The number specifies fractional seconds precision. NOW(3) returns three fractional digits, while the maximum supported precision is 6.

Q14. What is the difference between NOW() and SYSDATE()?

NOW() returns the statement-start time, while SYSDATE() returns the time when it executes.

Q15. Do date and time functions depend on time-zone settings?

Current-date and current-time functions use the MySQL session time zone. Applications should configure and document their time-zone strategy consistently.

19. Quick Revision Checklist

• NOW(): Returns the current date and time.
• NOW(fsp): Supports fractional seconds precision from 0 to 6.
• CURDATE(): Returns the current date only.
• DATE_FORMAT(date, format): Formats a date or datetime as a string.
• %Y: Four-digit year.
• %y: Two-digit year.
• %m: Two-digit month.
• %d: Two-digit day.
• %M: Full month name.
• %H: 24-hour clock.
• %h: 12-hour clock.
• %i: Minutes.
• %s: Seconds.
• %p: AM or PM.
• Use a date range to filter all records from a day when the column contains both date and time.
• Remember that DATE_FORMAT() changes presentation, not the underlying stored date.
• Current-date and current-time results depend on the session time zone.

20. Official Documentation and Further Learning

For complete syntax, additional format specifiers, and detailed function behavior, refer to the official MySQL documentation.

• MySQL 8.4 – Date and Time Functions
• MySQL 8.4 – Date and Time Data Types
• MySQL 8.4 – Time Zone Support
• Explore the MySQL Training Course at JustAcademy
• Register for a JustAcademy Course Demo

21. Conclusion

The MySQL date and time functions NOW(), CURDATE(), and DATE_FORMAT() are useful for managing timestamps, filtering records by date, and preparing readable reports. NOW() returns the current date and time, CURDATE() returns the current date, and DATE_FORMAT() formats a date or datetime into a chosen string representation.

Understanding these functions helps you work with employee joining dates, order records, customer registrations, appointment schedules, monthly reports, and other time-based data. Practice the examples, pay attention to format specifiers and time-zone behavior, and use date ranges carefully when filtering datetime columns.

For additional learning, visit the MySQL Training Course or register for a course demo.